package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.DailyProgram;
import bg.cinema_system.utils.Constants;

public class DailyProgramListDAO {

	// Connect to DB and get DailyProgram list
	public static List<DailyProgram> getDailyProgramList(long interval)
			throws SQLException {
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException(
					"Class DailyProgramListDAO: Can't get database connection");
		}

		String sqlStatement = "";

		if (interval == 0) {
			sqlStatement = "SELECT projections.projection_id, movies.movie_id, movies.name, projections.projection_start "
					+ "FROM projections "
					+ "INNER JOIN movies "
					+ "ON projections.fk_movie_id = movies.movie_id "
					+ "WHERE projections.projection_start >= NOW() + INTERVAL ? MINUTE "
					+ "AND projections.projection_start < CURDATE() + INTERVAL '1' DAY "
					+ "ORDER BY movies.name";
		} else {
			sqlStatement = "SELECT projections.projection_id, movies.movie_id, movies.name, projections.projection_start "
					+ "FROM projections "
					+ "INNER JOIN movies "
					+ "ON projections.fk_movie_id = movies.movie_id "
					+ "WHERE projections.projection_start >= CURDATE() + INTERVAL ? DAY "
					+ "AND projections.projection_start < CURDATE() + INTERVAL ? DAY "
					+ "ORDER BY movies.name";
		}

		PreparedStatement ps = dbConnection.prepareStatement(sqlStatement);
		if (interval == 0) {
			ps.setLong(1, Constants.EXPIRE_BEFORE_PROJECTION);
		} else {
			ps.setLong(1, interval);
			ps.setLong(2, interval + 1);
		}

		// Get customer data from database
		ResultSet result = ps.executeQuery();

		List<DailyProgram> dailyProgramList = new ArrayList<DailyProgram>();

		while (result.next()) {
			// Create Program object and assign it to null (have to check if
			// such already exists in the list)
			DailyProgram program = null;

			// Create an empty movie_start and movie_projection_id arrays with
			// 15 items each
			String[] movie_start = new String[15];
			String[] movie_projection_id = new String[15];

			// Check if the Program object is already created and if so assign
			// it to "program" object
			for (int i = 0; i < dailyProgramList.size(); i++) {
				if (dailyProgramList.get(i).getName()
						.equals(result.getString("name"))) {
					program = dailyProgramList.get(i);

					movie_start = dailyProgramList.get(i).getMovie_start();
					movie_projection_id = dailyProgramList.get(i)
							.getMovie_projection_id();

					dailyProgramList.remove(i);
					break;
				}
			}

			// If the Program object is not in the list, assign empty values
			if (program == null) {
				program = new DailyProgram();

				for (int i = 0; i < 15; i++) {
					movie_start[i] = "";
					movie_projection_id[i] = "";
				}
			}

			// Get the "start time" and the "projection id" of the projection of
			// the movie
			Date tempProjectionStart = result.getTime("projection_start");
			Integer hour = null;
			String start_time = "";
			String projection_id = "" + result.getLong("projection_id");

			// Check if the start time is valid and if so, get the hour and the
			// start_time (which will be visible in the table)
			try {
				hour = Integer.parseInt(tempProjectionStart.toString()
						.substring(0,
								tempProjectionStart.toString().indexOf(":")));
				start_time = tempProjectionStart.toString().substring(0,
						tempProjectionStart.toString().lastIndexOf(":"));
			} catch (Exception e) {
			}

			// Check on which position to put the "start time" and the
			// "projection id" in the program table
			if (hour != null) {
				if (hour >= 0 && hour < 1) {
					movie_projection_id[0] = projection_id;
					movie_start[0] = start_time;
				}
				if (hour >= 10 && hour < 11) {
					movie_projection_id[1] = projection_id;
					movie_start[1] = start_time;
				}
				if (hour >= 11 && hour < 12) {
					movie_projection_id[2] = projection_id;
					movie_start[2] = start_time;
				}
				if (hour >= 12 && hour < 13) {
					movie_projection_id[3] = projection_id;
					movie_start[3] = start_time;
				}
				if (hour >= 13 && hour < 14) {
					movie_projection_id[4] = projection_id;
					movie_start[4] = start_time;
				}
				if (hour >= 14 && hour < 15) {
					movie_projection_id[5] = projection_id;
					movie_start[5] = start_time;
				}
				if (hour >= 15 && hour < 16) {
					movie_projection_id[6] = projection_id;
					movie_start[6] = start_time;
				}
				if (hour >= 16 && hour < 17) {
					movie_projection_id[7] = projection_id;
					movie_start[7] = start_time;
				}
				if (hour >= 17 && hour < 18) {
					movie_projection_id[8] = projection_id;
					movie_start[8] = start_time;
				}
				if (hour >= 18 && hour < 19) {
					movie_projection_id[9] = projection_id;
					movie_start[9] = start_time;
				}
				if (hour >= 19 && hour < 20) {
					movie_projection_id[10] = projection_id;
					movie_start[10] = start_time;
				}
				if (hour >= 20 && hour < 21) {
					movie_projection_id[11] = projection_id;
					movie_start[11] = start_time;
				}
				if (hour >= 21 && hour < 22) {
					movie_projection_id[12] = projection_id;
					movie_start[12] = start_time;
				}
				if (hour >= 22 && hour < 23) {
					movie_projection_id[13] = projection_id;
					movie_start[13] = start_time;
				}
				if (hour >= 23 && hour < 24) {
					movie_projection_id[14] = projection_id;
					movie_start[14] = start_time;
				}
			}

			// Assign the variables to the "program" object
			program.setMovie_id("" + result.getLong("movie_id"));
			program.setName(result.getString("name"));
			program.setMovie_start(movie_start);
			program.setMovie_projection_id(movie_projection_id);
			program.setProgramInfo(Constants.INFORMATION_OK);

			// Store all data into a List
			dailyProgramList.add(program);
		}

		// Check if there are any films for this day
		if (dailyProgramList.isEmpty()) {
			DailyProgram program = new DailyProgram();
			program.setProgramInfo(Constants.INFORMATION_KO);

			dailyProgramList.add(program);
		}

		// Close all connections
		ps.close();
		result.close();
		dbConnection.close();

		return dailyProgramList;
	}
}